package com.slipper.datasource;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.slipper.PaasContext;
import com.slipper.codeless.resolve.SqlBuilderFactory;
import com.slipper.codeless.resolve.delete.Delete;
import com.slipper.codeless.resolve.update.Update;
import com.slipper.codeless.resolve.update.UpdateField;
import com.slipper.common.dao.CommonDao;
import com.slipper.exception.CommonException;
import com.slipper.system.codeless.bo.DatasourceBO;
import com.slipper.system.codeless.service.DatasourceService;
import com.slipper.util.Tool;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;

import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DatasourceUtil {
    private static final Map<String, SqlSessionFactory> factoryMap = new HashMap<>();

    public static void clean(String code) {
        if (Tool.isBlank(code)) return;

        factoryMap.remove(code);
    }

    private static SqlSessionFactory getSqlSessionFactory(DatasourceBO datasource) {
        if (Tool.isBlank(datasource.getCode())) throw new CommonException("数据源编码不能为空！");

        if (factoryMap.containsKey(datasource.getCode())) {
            return factoryMap.get(datasource.getCode());
        }

        try {
            String url = datasource.getDbUrl()
                    .replace("{ip}", datasource.getDbIp())
                    .replace("{port}", datasource.getDbPort() + "")
                    .replace("{database}", datasource.getDbName());

            Properties properties = new Properties();
            properties.put("dbType", datasource.getDatabaseType());
            properties.put("driver", datasource.getDbDriver());
            properties.put("url", url);
            properties.put("username", datasource.getDbUsername());
            properties.put("password", datasource.getDbPassword());

            String resource = "com/slipper/datasource/mybatis.xml";
            Reader reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader, properties);
            factoryMap.put(datasource.getCode(), sqlSessionFactory);
            return sqlSessionFactory;
        } catch (Exception e) {
            throw new CommonException("获取数据源连接失败！", e);
        }
    }

    public static List<Map<String, Object>> getMetaData(String code, String sql, Map<String, Object> paramMap) {
        return getMetaData(queryByCode(code), sql, paramMap);
    }

    public static List<Map<String, Object>> getMetaData(
            DatasourceBO datasource,
            String sql,
            Map<String, Object> paramMap) {
        try(SqlSession sqlSession = getSqlSessionFactory(datasource).openSession()) {
            return getMetaData(sqlSession, sql, paramMap);
        }
    }

    public static List<Map<String, Object>> getMetaData(
            SqlSession sqlSession,
            String sql,
            Map<String, Object> paramMap) {
        try {
            Connection connection = sqlSession.getConnection();
            return getMetaData(connection, sql, paramMap);
        } catch (Exception e) {
            throw new CommonException("sql解释出错，" + e.getMessage(), e);
        }
    }

    private static List<Map<String, Object>> getMetaData(
            Connection connection,
            String sql,
            Map<String, Object> paramMap) {
        PreparedStatement statement = null;
        try {
            if (paramMap != null && !paramMap.isEmpty()) {
                String sqlReal = sql;
                Pattern pattern = Pattern.compile("#\\{(.*?(?=}))}");
                Matcher matcher = pattern.matcher(sqlReal);
                List<Object> paramList = new ArrayList<>();
                while(matcher.find()) {
                    sqlReal = sqlReal.replace(matcher.group(), "?");
                    paramList.add(paramMap.get(matcher.group(1)));
                }
                statement = connection.prepareStatement(sqlReal);
                for (int i=0; i<paramList.size(); i++) {
                    statement.setObject(i + 1, paramList.get(i));
                }
            } else {
                statement = connection.prepareStatement(sql);
            }
            statement.setMaxRows(1);
            ResultSetMetaData metaData = statement.executeQuery().getMetaData();
            List<Map<String, Object>> resultList = new ArrayList<>();
            for (int i=0; i<metaData.getColumnCount(); i++) {
                Map<String, Object> result = new HashMap<>();
                result.put("columnName", metaData.getColumnName(i + 1));
                result.put("columnLabel", metaData.getColumnLabel(i + 1));
                result.put("columnType", metaData.getColumnType(i + 1));
                result.put("columnTypeName", metaData.getColumnTypeName(i + 1));
                result.put("columnClassName", metaData.getColumnClassName(i + 1));
                result.put("columnSize", metaData.getColumnDisplaySize(i + 1));
                result.put("columnPrecision", metaData.getScale(i + 1));
                result.put("columnScale", metaData.getScale(i + 1));
                resultList.add(result);
            }
            return resultList;
        } catch (Exception e) {
            throw new CommonException("sql解释出错，" + e.getMessage(), e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception ignore) {}
            }
        }
    }

    public static void update(String code, String sql) {
        update(code, sql, new HashMap<>());
    }

    public static void update(String code, String sql, Map<String, Object> paramMap) {
        update(code, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static void update(String code, Map<String, Object> paramMap) {
        update(queryByCode(code), paramMap);
    }

    public static void update(DatasourceBO datasource, String sql, Map<String, Object> paramMap) {
        update(datasource, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static void update(DatasourceBO datasource, Map<String, Object> paramMap) {
        try (SqlSession sqlSession = getSqlSessionFactory(datasource).openSession()) {
            sqlSession.getMapper(CommonDao.class).update(paramMap);
            sqlSession.commit();
        }
    }

    public static void update(
            DatasourceBO datasource,
            String tableName,
            String pkIdName,
            List<Map<String, Object>> dataList) {
        update(datasource, tableName, pkIdName, dataList, null);
    }

    public static void update(
            DatasourceBO datasource,
            String tableName,
            String pkIdName,
            List<Map<String, Object>> dataList,
            List<String> columnList) {
        if (dataList == null || dataList.isEmpty()) return;

        try (SqlSession sqlSession = getSqlSessionFactory(datasource).openSession()) {
            CommonDao commonDao = sqlSession.getMapper(CommonDao.class);
            dataList.forEach(data -> {
                Update update = new Update();
                update.setTableName(tableName);
                if (Tool.isNotBlank(pkIdName)) {
                    update.setPkIdName(pkIdName);
                    update.setPkIdValue(data.get(pkIdName));
                }

                if (columnList == null || columnList.isEmpty()) {
                    data.forEach((key, value) -> update.addUpdateField(new UpdateField(key, value)));
                } else {
                    columnList.forEach(column -> update.addUpdateField(new UpdateField(column, data.get(column))));
                }

                if (update.isInsert()) {
                    commonDao.insert(SqlBuilderFactory.buildUpdateMap(datasource.getDatabaseType(), update));
                } else {
                    commonDao.update(SqlBuilderFactory.buildUpdateMap(datasource.getDatabaseType(), update));
                }
            });
            sqlSession.commit();
        }
    }

    public static Map<String, Object> queryOne(String code, String sql) {
        return queryOne(code, sql, new HashMap<>());
    }

    public static Map<String, Object> queryOne(String code, String sql, Map<String, Object> paramMap) {
        return queryOne(code, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static Map<String, Object> queryOne(String code, Map<String, Object> paramMap) {
        return queryOne(queryByCode(code), paramMap);
    }

    public static Map<String, Object> queryOne(DatasourceBO datasource, String sql, Map<String, Object> paramMap) {
        return queryOne(datasource, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static Map<String, Object> queryOne(DatasourceBO datasource, Map<String, Object> paramMap) {
        try (SqlSession sqlSession = getSqlSessionFactory(datasource).openSession()) {
            CommonDao commonDao = sqlSession.getMapper(CommonDao.class);
            return commonDao.queryOne(paramMap);
        }
    }

    public static long queryTotal(DatasourceBO datasource, Map<String, Object> paramMap) {
        Map<String, Object> data = queryOne(datasource, new HashMap<>() {{
            putAll(paramMap);
            put("sql", "select count(*) cnt from (" + paramMap.get("sql") + ") c");
        }});

        if (data != null) {
            if (data.get("cnt") != null) {
                return Long.parseLong(data.get("cnt").toString());
            } else if (data.get("CNT") != null) {
                return Long.parseLong(data.get("CNT").toString());
            }
        }

        return 0L;
    }

    public static List<Map<String, Object>> queryList(String code, String sql) {
        return queryList(code, sql, new HashMap<>());
    }

    public static List<Map<String, Object>> queryList(String code, String sql, Map<String, Object> paramMap) {
        return queryList(code, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static List<Map<String, Object>> queryList(String code, Map<String, Object> paramMap) {
        return queryList(queryByCode(code), paramMap);
    }

    public static List<Map<String, Object>> queryList(
            DatasourceBO datasource,
            String sql,
            Map<String, Object> paramMap) {
        return queryList(datasource, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static List<Map<String, Object>> queryList(DatasourceBO datasource, Map<String, Object> paramMap) {
        try (SqlSession sqlSession = getSqlSessionFactory(datasource).openSession()) {
            CommonDao commonDao = sqlSession.getMapper(CommonDao.class);
            return commonDao.queryList(paramMap);
        }
    }

    public static PageInfo<Map<String, Object>> queryPage(
            String code,
            String sql,
            int pageNumber,
            int pageSize,
            Map<String, Object> paramMap) {
        return queryPage(code, pageNumber, pageSize, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static PageInfo<Map<String, Object>> queryPage(
            DatasourceBO datasource,
            String sql,
            int pageNumber,
            int pageSize,
            Map<String, Object> paramMap) {
        return queryPage(datasource, pageNumber, pageSize, new HashMap<>() {{
            if (paramMap != null) {
                putAll(paramMap);
            }
            put("sql", sql);
        }});
    }

    public static PageInfo<Map<String, Object>> queryPage(
            String code,
            int pageNumber,
            int pageSize,
            Map<String, Object> paramMap) {
        return queryPage(queryByCode(code), pageNumber, pageSize, paramMap);
    }

    public static PageInfo<Map<String, Object>> queryPage(
            DatasourceBO datasource,
            int pageNumber,
            int pageSize,
            Map<String, Object> paramMap) {
        try (SqlSession sqlSession = getSqlSessionFactory(datasource).openSession()) {
            CommonDao commonDao = sqlSession.getMapper(CommonDao.class);
            PageHelper.startPage(pageNumber, pageSize);
            return new PageInfo<>(commonDao.queryList(paramMap));
        }
    }

    public static PageInfo<Map<String, Object>> queryPage(
            SqlSession sqlSession,
            String sql,
            int pageNumber,
            int pageSize,
            Map<String, Object> data) {
        Map<String, Object> paramMap = new HashMap<>() {{
            if (data != null) {
                putAll(data);
            }
            put("sql", sql);
        }};

        CommonDao commonDao = sqlSession.getMapper(CommonDao.class);
        PageHelper.startPage(pageNumber, pageSize);
        return new PageInfo<>(commonDao.queryList(paramMap));
    }

    public static DatasourceBO queryByCode(String code) {
        DatasourceBO datasource = PaasContext.getBean(DatasourceService.class).queryByCode(code);
        if (datasource == null) throw new CommonException("数据编码不存在！");
        return datasource;
    }

    public static void deleteAll(DatasourceBO datasource, String tableName) {
        Delete delete = new Delete();
        delete.setTableName(tableName);
        delete.setCleanTable(true);
        update(datasource, SqlBuilderFactory.buildDeleteMap(datasource.getDatabaseType(), delete));
    }
}
